package edu.geo.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import edu.geo.containers.geomdomain.Grid;
import edu.geo.dao.IGeometryDao;
import edu.geo.dao.IGridDao;
import edu.geo.dao.exception.PersistentExceptions;
import edu.geo.db.conn.DBConnection;
import edu.geo.db.conn.DBConnectionProvider;
import edu.geo.exception.GeoInfoException;

/**
 * 
 * @author alejandro.sanchez
 *
 */
public class GridDao implements IGridDao {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 4990235622592270501L;

	private static final String INSERT_GRID = "INSERT INTO geometric_domains.grid(" +
        "gr_id, gd_id, gr_num_points_x, gr_num_points_y, gr_origin, gr_int_size) VALUES (?, ?, ?, ?, ?, ?)";
	
	private static final String SELECT_GRID_ID = "SELECT nextval('geometric_domains.grid_gr_id_seq')";
	
	private static final String SELECT_GRID = "SELECT gr_id, gd_id, gr_num_points_x, gr_num_points_y, gr_int_size, gr_origin FROM geometric_domains.grid WHERE gr_id = {0}";
	
	private static final String SELECT_GRID_GEOM_DOMAIN_ID = "SELECT gr_id FROM geometric_domains.grid WHERE gd_id = {0}";
	
	private static final String DELETE_GRID = "DELETE FROM geometric_domains.grid WHERE gr_id = {0}";

	private static final String DELETE_GRID_ORIGIN = "DELETE FROM geometric_domains.point WHERE fe_id = {0}";
	
	private static final Log LOGGER = LogFactory.getLog(GridDao.class);
	
	private DBConnectionProvider connectionProvider;
	
	private IGeometryDao geometryDao;
	
	public GridDao(DBConnectionProvider connectionProvider) {
		super();
		this.connectionProvider = connectionProvider;
		geometryDao = new GeometryDao(connectionProvider);
	}

	@Override
	public int insertGrid(Grid grid) throws GeoInfoException{				
		LOGGER.info("insertGrid");
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;
		int gId = -1;
		try {
			conn = connectionProvider.getConnection();
			//conn.setAutoCommit(false);
			//Obtener identificador de la grilla
			statement = conn.createStatement();
			result = statement.executeQuery(SELECT_GRID_ID);
			if ( result.next() )
			{
				gId = result.getInt(1);				
				//Insertar registro
				PreparedStatement insertStatement = conn.prepareStatement(INSERT_GRID);
				//gr_id
				insertStatement.setInt(1, gId);
				//gd_id: Dominio geométrico al que pertenece la grilla. Una grilla no puede
				//       existir si no existe un dominio geométrico.
				insertStatement.setInt(2, grid.getGeomDomainId());
				//gr_num_points_x
				insertStatement.setInt(3, grid.getNumPointsX());
				//gr_num_points_y
				insertStatement.setInt(4, grid.getNumPointsY());
				/*gr_origin: Si el origen no existe, entonces se agrega al modelo y el id de dicho
				 *           punto es agregado aquí
				*/  
				int idOrigin = geometryDao.verifyPointExistence(grid.getOrigin());
				if (idOrigin < 0)
				{
					idOrigin = geometryDao.insertPoint(grid.getOrigin());
					grid.getOrigin().setId(idOrigin);
					
				}
				insertStatement.setInt(5, idOrigin);
				//gr_int_size
				insertStatement.setInt(6, grid.getIntSize());
				insertStatement.execute();
				
			}
			//conn.commit();
			return gId;		
		} catch (SQLException e) {
			throw PersistentExceptions.INSERT_GRID_ERROR.getExcepcion(LOGGER, e);			
		}		
		finally
		{
			DBConnection.close(result,statement,conn);
		}
	}
	
	
	@Override
	public Grid retrieveGrid(int id)  throws GeoInfoException {
		LOGGER.info("retrieveGrid");
		Connection conn = null;
		Statement statement = null;
		ResultSet result = null;
		Grid grid;
		if ( id == Grid.DEFAULT_GRID_ID )
		{
			throw PersistentExceptions.INVALID_GRID_ID.getExcepcion(LOGGER, null);
		}
		try {
			grid = new Grid();
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			result = statement.executeQuery(MessageFormat.format(SELECT_GRID, id));
			if (result.next())
			{		
				grid.setId(id);
				grid.setGeomDomainId(result.getInt("gd_id"));
				grid.setIntSize(result.getInt("gr_int_size"));
				grid.setNumPointsX(result.getInt("gr_num_points_x"));
				grid.setNumPointsY(result.getInt("gr_num_points_y"));
				grid.setOrigin(geometryDao.retrievePoint(result.getInt("gr_origin")));								
			}
			else
			{
				throw PersistentExceptions.GRID_NOT_EXISTS.getExcepcion(LOGGER, null, id);
			}
			return grid;			
		} catch (SQLException e) {
			throw PersistentExceptions.DB_EXCEPTION.getExcepcion(LOGGER, e);
		}
		finally
		{
			DBConnection.close(result, statement, conn);
		}
	}

	@Override
	public void deleteGrid(int id) throws GeoInfoException {
		LOGGER.info("deleteGrid");	
		Connection conn = null;
		Statement statement = null;
		try {
			Grid grid = retrieveGrid(id);
			conn = connectionProvider.getConnection();			
			statement = conn.createStatement();
			statement.executeUpdate(MessageFormat.format(DELETE_GRID, id));
			statement.executeUpdate(MessageFormat.format(DELETE_GRID_ORIGIN, grid.getOrigin().getId()));
		} catch (SQLException e) {
			throw PersistentExceptions.DELETE_GRID_ERROR.getExcepcion(LOGGER, e, id);			
		}		
		finally
		{
			DBConnection.close(statement,conn);
		}		
	}

	@Override
	public int retrieveGridIdByGeomDomainId(int id) throws GeoInfoException {
		Connection conn = null;
		Statement statement = null;
		ResultSet resultSet = null;
		int gId = -1;
		try {
			conn = connectionProvider.getConnection();
			statement = conn.createStatement();
			resultSet = statement.executeQuery(MessageFormat.format(SELECT_GRID_GEOM_DOMAIN_ID, id));
			if ( resultSet.next() )
			{
				gId = resultSet.getInt(1);
			}			
		} catch (SQLException e) {
			throw PersistentExceptions.RETRIEVE_GRID_ERROR.getExcepcion(LOGGER, e, id);			
		}		
		finally
		{
			DBConnection.close(resultSet, statement, conn);
		}
		return gId;
	}

	@Override
	public void deleteGridByGeomDomainId(int geomDomainId)
			throws GeoInfoException {
		LOGGER.info("deleteGrid");
		int gridId = retrieveGridIdByGeomDomainId( geomDomainId );
		deleteGrid(gridId);
	}

	@Override
	public Grid retrieveGridGeomDomain(int geomDomainId) throws GeoInfoException {
		int id = retrieveGridIdByGeomDomainId(geomDomainId);
		return retrieveGrid(id);
	}	
}
